import logging
import math
import time
import json
import sys
from operator import itemgetter

import pandas as pd

from src.db.CcxDataCenterDb import CcxDataCenter
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger

from itertools import groupby

logger = Logger()


# 同步产业所有行业年度数据
class RegionsData:
    def __init__(self, logger):
        self.logger = logger
        self.dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)

    def __regionSql__(self, code):
        sql = """
            SELECT
	            # d.id,
	            # d.annual_data_id,
	CAST(d.target_value AS FLOAT8) AS `value`,
	d.`year`,
	CASE 
	WHEN d.`code` IS NULL THEN
		%s
	ELSE
		d.`code`
END  AS `code`,
	c.third_target_code AS target_code,
	c.third_target_name AS target_name 
FROM
	(
	SELECT
		b.*,
		a.`year`,
		a.`code` 
	FROM
		`regions_annual_data_detail` b
		LEFT JOIN regions_annual_data a ON a.id = b.annual_data_id 
	WHERE
		annual_data_id IN (
		SELECT
			id 
		FROM
			(
			SELECT
				sub_b.*,
				sub_d.`name`,
				sub_d.`type`,
				sub_d.LEVEL,
				sub_d.province,
				sub_d.province_code,
				sub_d.city,
				sub_d.city_code,
				sub_d.county,
				sub_d.county_code,
				sub_d.p_functional,
				sub_d.p_functional_code,
				sub_d.functional,
				sub_d.functional_code 
			FROM
				(
				SELECT
					sub_c.* 
				FROM
					regions_annual_data sub_c 
				WHERE
					sub_c.ver = 0 UNION
				SELECT
					sub_c.* 
				FROM
					regions_annual_data sub_c 
				WHERE
					release_status = 0 
				ORDER BY
					last_edit_time DESC 
				) sub_b
				LEFT JOIN regions sub_d ON sub_d.ver = 0 
				AND sub_d.release_status = 1 
				AND sub_b.`code` = sub_d.`code` 
			WHERE
				sub_b.CODE in (%s)
			GROUP BY
				CODE,
			YEAR 
			ORDER BY
				release_status ASC,
				`last_edit_time` DESC 
			) g 
		) 
	) d
	RIGHT JOIN ( SELECT * FROM `regions_annual_data_code` WHERE release_status = 1 AND ver = 0 AND `status` = 1 ) c ON d.target_code = c.third_target_code 
	AND d.target_code IN ( 'QY010101', 'QY020102', 'QY020105', 'QY010503', 'QY020103', 'QY030201' ) 
ORDER BY
	target_code
        """
        return self.dataCenterDb.Query(sql, (code, code))

    def regionData(self, code):
        dataInfo = self.__regionSql__(code)
        dataInfo.sort(key=itemgetter("code"))
        data = groupby(dataInfo, key=itemgetter("code"))
        for code, items in data:
            dataList = list(items)
        return dataList



def main():
    logger = Logger()
    cleanScreen()
    t = time.perf_counter()
    sync = RegionsData(logger=logger)
    sync.regionData(code=120000)
    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))


# 程序入口
if __name__ == "__main__":
    main()
